/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package control;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import model.*;
import utils.DateUtils;

/**
 *
 * @author fred
 */
public class MessageDAO {
    
    private static final String SELECT_COMMENTS = "SELECT MESSAGES.message_id,"
                + "MESSAGES.message_text,"
                + "MESSAGES.message_dateTime,"
                + "USERS.user_login,"
                + "USERS.user_password,"
                + "USERS.user_name,"
                + "USERS.user_email,"
                + "USERS.user_birthDate "
            + "FROM MESSAGES "
            + "INNER JOIN COMMENTS ON COMMENTS.message_id = MESSAGES.message_id "
            + "INNER JOIN USERS ON USERS.user_login = MESSAGES.user_login";
    
    private Connection con;
    
    public MessageDAO(Connection con) {
        this.con = con;
    }
    
    public boolean insertMessage(Message message) {
        try {
            ResultSet rs = con.query("SELECT InsertMessage('" + message.getText() + "',"
                    + "'" + DateUtils.formatDateTime(message.getDateTime()) + "',"
                    + "'" + message.getAuthor().getLogin() + "')");
            if (rs.next()) {
                int id = rs.getInt(1);
                message.setId(id);
                
                return true;
            }
        } catch (OperationNotAllowedException ex) {
            ex.printStackTrace();
        } catch (InvalidValueException ex) {
            ex.printStackTrace();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return false;
    }
    
    public boolean editMessage(Message newMessage) {
        try {
            con.command("UPDATE MESSAGES SET message_text = '" + newMessage.getText() + "',"
                        + "message_dateTime = '" + DateUtils.formatDateTime(newMessage.getDateTime()) + "',"
                        + "user_login = '" + newMessage.getAuthor().getLogin() + "'"
                    + "WHERE message_id = " + newMessage.getId());
            return true;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return false;
    }
    
    public boolean deleteMessage(Message message) {
        try {
            con.command("DELETE FROM MESSASGES WHERE message_id = " + message.getId());
            return true;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return false;
    }
    
    public boolean insertComment(Message message, Message comment) {
        try {
            ResultSet rs = con.query("SELECT InsertComment('" + comment.getText() + "',"
                    + "'" + DateUtils.formatDateTime(comment.getDateTime()) + "',"
                    + "'" + comment.getAuthor().getLogin() + "',"
                    + message.getId() + ")");
            if (rs.next()) {
                int id = rs.getInt(1);
                comment.setId(id);
                
                return true;
            }
        } catch (OperationNotAllowedException ex) {
            ex.printStackTrace();
        } catch (InvalidValueException ex) {
            ex.printStackTrace();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return false;
    }
    
    public boolean editComment(Message newComment) {
        return editMessage(newComment);
    }
    
    public boolean deleteComment(Message comment) {
        return deleteMessage(comment);
    }
    
    public Message getMessage(int id) {
        try {
            ResultSet rs = con.query("SELECT MESSAGES.message_id,"
                        + "MESSAGES.message_text,"
                        + "MESSAGES.message_dateTime, "
                        + "USERS.user_login,"
                        + "USERS.user_password,"
                        + "USERS.user_name,"
                        + "USERS.user_email,"
                        + "USERS.user_birthDate "
                    + "FROM MESSAGES "
                    + "INNER JOIN USERS ON USERS.user_login = MESSAGES.user_login "
                    + "WHERE MESSAGES.message_id = " + id);
            if (rs.next()) {
                int messageId           = rs.getInt("message_id");
                String messageText      = rs.getString("message_text");
                Date messageDateTime    = rs.getTimestamp("message_dateTime");
                String userLogin        = rs.getString("user_login");
                String userPassword     = rs.getString("user_password");
                String userName         = rs.getString("user_name");
                String userEmail        = rs.getString("user_email");
                Date userBirthdate      = rs.getDate("user_birthDate");
                User user = new User(userLogin, userPassword, userName,
                        userEmail, userBirthdate);
                Message message = new Message(messageId, user, messageText, 
                        messageDateTime, new ArrayList<Message>());
                
                // Used to verify the User objects already created and reuse them
                HashMap<String,User> usersAlready = new HashMap<String,User>();
                usersAlready.put(user.getLogin(), user);

                // Message comments
                ResultSet rsComm = con.query(SELECT_COMMENTS +
                        " WHERE COMMENTS.commented_message_id = " + messageId);
                while (rsComm.next()) {
                    messageId           = rsComm.getInt("message_id");
                    messageText         = rsComm.getString("message_text");
                    messageDateTime     = rsComm.getTimestamp("message_dateTime");
                    userLogin           = rsComm.getString("user_login");
                    userPassword        = rsComm.getString("user_password");
                    userName            = rsComm.getString("user_name");
                    userEmail           = rsComm.getString("user_email");
                    userBirthdate       = rsComm.getDate("user_birthDate");
                    // Checks user
                    User u;
                    if (usersAlready.containsKey(userLogin))
                        u = usersAlready.get(userLogin);
                    else {
                        u = new User(userLogin, userPassword, userName, userEmail,
                                userBirthdate);
                        usersAlready.put(u.getLogin(), u);
                    }
                    Message comment = new Message(messageId, u, messageText, messageDateTime);
                    
                    message.addComment(comment);
                }
                // End of message comments
                
                return message;
            }
        } catch (InvalidValueException ex) {
            ex.printStackTrace();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }
    
    public ArrayList<Message> getMessages(User user) {
        ArrayList<Message> messages = new ArrayList<Message>();
        try {
            ResultSet rs = con.query("SELECT MESSAGES.message_id,"
                        + "MESSAGES.message_text,"
                        + "MESSAGES.message_dateTime "
                    + "FROM MESSAGES "
                    + "WHERE MESSAGES.user_login = '" + user.getLogin() + "' "
                        + "AND MESSAGES.message_id NOT IN (SELECT message_id FROM COMMENTS) "
                    + "ORDER BY MESSAGES.message_dateTime DESC");
            while (rs.next()) {
                int messageId           = rs.getInt("message_id");
                String messageText      = rs.getString("message_text");
                Date messageDateTime    = rs.getTimestamp("message_dateTime");
                Message message = new Message(messageId, user, messageText, 
                        messageDateTime, new ArrayList<Message>());
                
                // Used to verify the User objects already created and reuse them
                HashMap<String,User> usersAlready = new HashMap<String,User>();
                usersAlready.put(user.getLogin(), user);

                // Message comments
                ResultSet rsComm = con.query(SELECT_COMMENTS +
                        " WHERE COMMENTS.commented_message_id = " + messageId);
                while (rsComm.next()) {
                    messageId           = rsComm.getInt("message_id");
                    messageText         = rsComm.getString("message_text");
                    messageDateTime     = rsComm.getTimestamp("message_dateTime");
                    String userLogin    = rsComm.getString("user_login");
                    String userPassword = rsComm.getString("user_password");
                    String userName     = rsComm.getString("user_name");
                    String userEmail    = rsComm.getString("user_email");
                    Date userBirthdate  = rsComm.getDate("user_birthDate");
                    // Checks user
                    User u;
                    if (usersAlready.containsKey(userLogin))
                        u = usersAlready.get(userLogin);
                    else {
                        u = new User(userLogin, userPassword, userName, userEmail,
                                userBirthdate);
                        usersAlready.put(u.getLogin(), u);
                    }
                    Message comment = new Message(messageId, u, messageText, messageDateTime);
                    
                    message.addComment(comment);
                }
                // End of message comments
                
                messages.add(message);
            }
        } catch (InvalidValueException ex) {
            ex.printStackTrace();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return messages;
    }
    
    public ArrayList<Message> getFollowedsMessages(User user) {
        ArrayList<Message> messages = new ArrayList<Message>();
        try {
            // Used to verify the User objects already created and reuse them
            HashMap<String,User> usersAlready = new HashMap<String,User>();
            usersAlready.put(user.getLogin(), user);
            
            ResultSet rs = con.query("(SELECT MESSAGES.message_id, "
                            + "MESSAGES.message_text, "
                            + "MESSAGES.message_dateTime, "
                            + "USERS.user_login, "
                            + "USERS.user_password, "
                            + "USERS.user_name, "
                            + "USERS.user_email, "
                            + "USERS.user_birthDate "
                        + "FROM MESSAGES "
                        + "INNER JOIN USERS ON MESSAGES.user_login = USERS.user_login "
                        + "INNER JOIN FOLLOWS ON USERS.user_login = FOLLOWS.followed_user_login "
                        + "WHERE FOLLOWS.follower_user_login = '" + user.getLogin() + "' "
                            + "AND MESSAGES.message_id NOT IN (SELECT message_id FROM COMMENTS)) "
                    + "UNION "
                        + "(SELECT MESSAGES.message_id, "
                            + "MESSAGES.message_text, "
                            + "MESSAGES.message_dateTime, "
                            + "USERS.user_login, "
                            + "USERS.user_password, "
                            + "USERS.user_name, "
                            + "USERS.user_email, "
                            + "USERS.user_birthDate "
                        + "FROM MESSAGES "
                        + "INNER JOIN USERS ON MESSAGES.user_login = USERS.user_login "
                        + "WHERE USERS.user_login = '" + user.getLogin() + "' "
                            + "AND MESSAGES.message_id NOT IN (SELECT message_id FROM COMMENTS)) "
                    + "ORDER BY message_dateTime DESC");
            while (rs.next()) {
                int messageId           = rs.getInt("message_id");
                String messageText      = rs.getString("message_text");
                Date messageDateTime    = rs.getTimestamp("message_dateTime");
                String userLogin        = rs.getString("user_login");
                String userPassword     = rs.getString("user_password");
                String userName         = rs.getString("user_name");
                String userEmail        = rs.getString("user_email");
                Date userBirthdate      = rs.getDate("user_birthDate");
                // Checks user
                User u;
                if (usersAlready.containsKey(userLogin))
                    u = usersAlready.get(userLogin);
                else {
                    u = new User(userLogin, userPassword, userName, userEmail,
                            userBirthdate);
                    usersAlready.put(u.getLogin(), u);
                }
                Message message = new Message(messageId, u, messageText, 
                        messageDateTime, new ArrayList<Message>());
                
                // Message comments
                ResultSet rsComm = con.query(SELECT_COMMENTS +
                        " WHERE COMMENTS.commented_message_id = " + messageId);
                while (rsComm.next()) {
                    messageId           = rsComm.getInt("message_id");
                    messageText         = rsComm.getString("message_text");
                    messageDateTime     = rsComm.getTimestamp("message_dateTime");
                    userLogin           = rsComm.getString("user_login");
                    userPassword        = rsComm.getString("user_password");
                    userName            = rsComm.getString("user_name");
                    userEmail           = rsComm.getString("user_email");
                    userBirthdate       = rsComm.getDate("user_birthDate");
                    // checks user
                    if (usersAlready.containsKey(userLogin))
                        u = usersAlready.get(userLogin);
                    else {
                        u = new User(userLogin, userPassword, userName, userEmail,
                                userBirthdate);
                        usersAlready.put(u.getLogin(), u);
                    }
                    Message comment = new Message(messageId, u, messageText, messageDateTime);
                    
                    message.addComment(comment);
                }
                // End of message comments
                
                messages.add(message);
            }
        } catch (InvalidValueException ex) {
            ex.printStackTrace();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return messages;
    }
    
    public int getSubjectOccurrence(String expression) {
        try {
            ResultSet rs = con.query("SELECT COUNT(*) "
                    + "FROM MESSAGES "
                    + "WHERE MESSAGES.message_text LIKE '%" + expression + "%'");
            if (rs.next()) {
                int count = rs.getInt(1);
                return count;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return -1;
    }
    
}
